from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import declarative_base,relationship

from db import engine


Base = declarative_base()


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer)


# 一对多关系（User → Post）
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    user_id = Column(Integer, ForeignKey("users.id"))

    user = relationship("User", back_populates="posts")


User.posts = relationship("Post", order_by=Post.id, back_populates="user")


# 多对多关系（User ↔ Course）
# 关联表
user_course = Table(
    "user_course", Base.metadata,
    Column("user_id", Integer, ForeignKey("users.id")),
    Column("course_id", Integer, ForeignKey("courses.id"))
)


class Course(Base):
    __tablename__ = "courses"
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    users = relationship("User", secondary=user_course, back_populates="courses")

User.courses = relationship("Course", secondary=user_course, back_populates="users")



# 创建表
Base.metadata.create_all(engine)


